{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据查看"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 驱动器 C 中的卷是 OS\n",
      " 卷的序列号是 AE6F-00B3\n",
      "\n",
      " C:\\Users\\27420\\PycharmProjects\\训练\\git\\future-teddy\\code\\data 的目录\n",
      "\n",
      "2021/11/13  19:00    <DIR>          .\n",
      "2021/11/13  19:00    <DIR>          ..\n",
      "2021/11/13  18:37               448 Algeria国家增长率.csv\n",
      "2021/11/13  18:37               537 Angola国家增长率.csv\n",
      "2021/11/13  18:37               736 Benin国家增长率.csv\n",
      "2021/11/13  18:37               588 Botswana国家增长率.csv\n",
      "2021/11/13  18:37               660 Burkina Faso国家增长率.csv\n",
      "2021/11/13  18:37               570 Burundi国家增长率.csv\n",
      "2021/11/13  18:37               787 Cameroon国家增长率.csv\n",
      "2021/11/13  18:37               602 Central African Republic国家增长率.csv\n",
      "2021/11/13  18:37               508 Chad国家增长率.csv\n",
      "2021/11/13  18:37               620 Congo国家增长率.csv\n",
      "2021/11/13  18:37               201 contract_new.csv\n",
      "2021/11/13  18:37               802 Cote d'Ivoire国家增长率.csv\n",
      "2021/11/13  18:37               793 Democratic Republic of the Congo国家增长率.csv\n",
      "2021/11/13  18:37               434 Djibouti国家增长率.csv\n",
      "2021/11/13  18:55               929 Eastern增长率.csv\n",
      "2021/11/13  18:37               584 Egypt国家增长率.csv\n",
      "2021/11/13  18:37               613 Equatorial Guinea国家增长率.csv\n",
      "2021/11/13  18:37               660 Eritrea国家增长率.csv\n",
      "2021/11/13  18:37               545 Ethiopia国家增长率.csv\n",
      "2021/11/13  18:37               449 Gabon国家增长率.csv\n",
      "2021/11/13  18:37               830 Ghana国家增长率.csv\n",
      "2021/11/13  18:37               507 Guinea-Bissau国家增长率.csv\n",
      "2021/11/13  18:37               376 Guinea国家增长率.csv\n",
      "2021/11/13  18:37               594 Kenya国家增长率.csv\n",
      "2021/11/13  18:37               591 Lesotho国家增长率.csv\n",
      "2021/11/13  18:37               582 Liberia国家增长率.csv\n",
      "2021/11/13  18:37               607 Libya国家增长率.csv\n",
      "2021/11/13  18:37               595 Madagascar国家增长率.csv\n",
      "2021/11/13  18:37               821 Malawi国家增长率.csv\n",
      "2021/11/13  18:37               518 Mali国家增长率.csv\n",
      "2021/11/13  18:37               683 Mauritania国家增长率.csv\n",
      "2021/11/13  18:37               470 Mauritius国家增长率.csv\n",
      "2021/11/13  18:55               261 merge_data2.csv\n",
      "2021/11/13  18:55               873 Middle增长率.csv\n",
      "2021/11/13  18:37               548 Morocco国家增长率.csv\n",
      "2021/11/13  18:37               555 Mozambique国家增长率.csv\n",
      "2021/11/13  18:37               584 Namibia国家增长率.csv\n",
      "2021/11/13  18:37               858 Nigeria国家增长率.csv\n",
      "2021/11/13  18:37               639 Niger国家增长率.csv\n",
      "2021/11/13  18:55               854 Northern增长率.csv\n",
      "2021/11/13  18:55             5,634 quarter_ country.csv\n",
      "2021/11/13  18:55               228 quarter_1st.csv\n",
      "2021/11/13  18:55               198 quarter_2nd.csv\n",
      "2021/11/13  18:55               218 quarter_3rd.csv\n",
      "2021/11/13  18:55               184 quarter_4th.csv\n",
      "2021/11/13  18:55             6,283 quarter_service.csv\n",
      "2021/11/13  18:37               522 Rwanda国家增长率.csv\n",
      "2021/11/13  18:55             5,677 sale_01_country.csv\n",
      "2021/11/13  18:55               738 sale_01_region.csv\n",
      "2021/11/13  18:55               250 sale_last.csv\n",
      "2021/11/13  18:37               603 Sao Tome and Principe国家增长率.csv\n",
      "2021/11/13  18:37               450 Senegal国家增长率.csv\n",
      "2021/11/13  18:37               499 Seychelles国家增长率.csv\n",
      "2021/11/13  18:37               500 Sierra Leone国家增长率.csv\n",
      "2021/11/13  18:37               648 Somalia国家增长率.csv\n",
      "2021/11/13  18:37               854 South Africa国家增长率.csv\n",
      "2021/11/13  18:37               513 South Sudan国家增长率.csv\n",
      "2021/11/13  18:55               882 Southern增长率.csv\n",
      "2021/11/13  18:37               729 Sudan国家增长率.csv\n",
      "2021/11/13  18:37               467 Swaziland国家增长率.csv\n",
      "2021/11/13  18:37               604 Togo国家增长率.csv\n",
      "2021/11/13  18:37               469 Tunisia国家增长率.csv\n",
      "2021/11/13  18:37               680 Uganda国家增长率.csv\n",
      "2021/11/13  18:37               531 United Republic of Tanzania国家增长率.csv\n",
      "2021/11/13  18:37               541 Western Sahara国家增长率.csv\n",
      "2021/11/13  18:55               948 Western增长率.csv\n",
      "2021/11/13  18:55            11,859 year_country.csv\n",
      "2021/11/13  18:55               731 year_region.csv\n",
      "2021/11/13  18:55               930 year_service.csv\n",
      "2021/11/13  18:37               492 Zambia国家增长率.csv\n",
      "2021/11/13  18:37               553 Zimbabwe国家增长率.csv\n",
      "2021/11/13  08:13            74,501 非洲通讯产品销售数据.xlsx\n",
      "2021/11/13  08:48            62,328 国家销售情况.xlsx\n",
      "2021/11/13  08:32            14,110 销售合同签订情况.xlsx\n",
      "2021/11/13  18:52             2,638 预测_地区_利润.csv\n",
      "2021/11/13  18:52             2,631 预测_地区_销售额.csv\n",
      "2021/11/13  18:52            12,572 预测_国家_利润.csv\n",
      "2021/11/13  18:52            12,795 预测_国家_销售额.csv\n",
      "              78 个文件        250,402 字节\n",
      "               2 个目录 108,720,070,656 可用字节\n"
     ]
    }
   ],
   "source": [
    "ls data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导包\n",
    "\n",
    "import array\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import jieba\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "matplotlib.rcParams['font.sans-serif'] = ['SimHei'] \n",
    "matplotlib.rcParams['font.family']='sans-serif'\n",
    "matplotlib.rcParams['axes.unicode_minus'] = False "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "sale = pd.read_excel('data/国家销售情况.xlsx')\n",
    "contract = pd.read_excel('data/销售合同签订情况.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Abidjan</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>656.96</td>\n",
       "      <td>6.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>Antananarivo</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>875.94</td>\n",
       "      <td>-70.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Kigali</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>258.35</td>\n",
       "      <td>18.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Harare</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>875.62</td>\n",
       "      <td>-35.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-02</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>Addis Ababa</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>509.93</td>\n",
       "      <td>10.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期             国家            城市       地区         服务分类     销售额     利润\n",
       "0 2017-01-01  Cote d'Ivoire       Abidjan  Western   Commercial  656.96   6.57\n",
       "1 2017-01-01     Madagascar  Antananarivo  Eastern       Public  875.94 -70.08\n",
       "2 2017-01-01         Rwanda        Kigali  Eastern       Public  258.35  18.08\n",
       "3 2017-01-01       Zimbabwe        Harare  Eastern  Residential  875.62 -35.02\n",
       "4 2017-01-02       Ethiopia   Addis Ababa  Eastern  Residential  509.93  10.20"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sale.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>销售经理</th>\n",
       "      <th>地区</th>\n",
       "      <th>销售合同</th>\n",
       "      <th>成交率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Aiden Morris</td>\n",
       "      <td>Western</td>\n",
       "      <td>13</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Audrey Baker</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>11</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Constantine Eager</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>9</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Francis Martineau</td>\n",
       "      <td>Northern</td>\n",
       "      <td>11</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>George O'Malley</td>\n",
       "      <td>Middle</td>\n",
       "      <td>14</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期               销售经理        地区  销售合同   成交率\n",
       "0 2017-01-01       Aiden Morris   Western    13  0.55\n",
       "1 2017-01-01       Audrey Baker   Eastern    11  0.30\n",
       "2 2017-01-01  Constantine Eager   Eastern     9  0.36\n",
       "3 2017-01-01  Francis Martineau  Northern    11  0.22\n",
       "4 2017-01-01    George O'Malley    Middle    14  0.33"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contract.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 查看是否存在缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1056 entries, 0 to 1055\n",
      "Data columns (total 7 columns):\n",
      "日期      1056 non-null datetime64[ns]\n",
      "国家      1056 non-null object\n",
      "城市      1056 non-null object\n",
      "地区      1056 non-null object\n",
      "服务分类    1056 non-null object\n",
      "销售额     1056 non-null float64\n",
      "利润      1056 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(2), object(4)\n",
      "memory usage: 57.8+ KB\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 160 entries, 0 to 159\n",
      "Data columns (total 5 columns):\n",
      "日期      160 non-null datetime64[ns]\n",
      "销售经理    160 non-null object\n",
      "地区      160 non-null object\n",
      "销售合同    160 non-null int64\n",
      "成交率     160 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(1), object(2)\n",
      "memory usage: 6.3+ KB\n"
     ]
    }
   ],
   "source": [
    "sale.info()\n",
    "contract.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 重复值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sale删除重复值前： (1056, 7)\n",
      "sale删除重复值后： (1056, 7)\n",
      "contract删除重复值前： (160, 5)\n",
      "contract删除重复值后： (160, 5)\n"
     ]
    }
   ],
   "source": [
    "print('sale删除重复值前：', sale.shape)\n",
    "sale = sale.drop_duplicates()\n",
    "print('sale删除重复值后：', sale.shape)\n",
    "print('contract删除重复值前：', contract.shape)\n",
    "contract = contract.drop_duplicates()\n",
    "print('contract删除重复值后：', contract.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 没有异常值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 销售额后 10 名的国家排行榜"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_last = sale[['国家','销售额']].groupby(by=['国家']).agg({'销售额':'sum'}).sort_values(['销售额'], ascending = True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_last.to_csv('data/sale_last.csv',encoding=\"gbk\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 任务一 统计各个年度/季度中，地区、国家、服务分类的销售额和利润数据，并计算各国、各服务分类销售额和利润的同比增长率。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 先将日期分割成年度/季度"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 划分季度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "      <th>年度</th>\n",
       "      <th>季度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Abidjan</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>656.96</td>\n",
       "      <td>6.57</td>\n",
       "      <td>2017</td>\n",
       "      <td>第一季度</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>Antananarivo</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>875.94</td>\n",
       "      <td>-70.08</td>\n",
       "      <td>2017</td>\n",
       "      <td>第一季度</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Kigali</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>258.35</td>\n",
       "      <td>18.08</td>\n",
       "      <td>2017</td>\n",
       "      <td>第一季度</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Harare</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>875.62</td>\n",
       "      <td>-35.02</td>\n",
       "      <td>2017</td>\n",
       "      <td>第一季度</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-02</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>Addis Ababa</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>509.93</td>\n",
       "      <td>10.20</td>\n",
       "      <td>2017</td>\n",
       "      <td>第一季度</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期             国家            城市       地区         服务分类     销售额  \\\n",
       "0 2017-01-01  Cote d'Ivoire       Abidjan  Western   Commercial  656.96   \n",
       "1 2017-01-01     Madagascar  Antananarivo  Eastern       Public  875.94   \n",
       "2 2017-01-01         Rwanda        Kigali  Eastern       Public  258.35   \n",
       "3 2017-01-01       Zimbabwe        Harare  Eastern  Residential  875.62   \n",
       "4 2017-01-02       Ethiopia   Addis Ababa  Eastern  Residential  509.93   \n",
       "\n",
       "      利润    年度    季度  \n",
       "0   6.57  2017  第一季度  \n",
       "1 -70.08  2017  第一季度  \n",
       "2  18.08  2017  第一季度  \n",
       "3 -35.02  2017  第一季度  \n",
       "4  10.20  2017  第一季度  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_1['new_time'] = df_1['time'].dt.strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "sale['年度'] = sale['日期'].dt.strftime(\"%Y\")\n",
    "sale['季度'] = sale['日期'].dt.strftime(\"%m\")\n",
    "#使用pd_date_range筛选指定日期的数据\n",
    "sale['季度'] = sale['季度'].apply(lambda x : '第一季度' if x == '01' or x == '02' or x =='03' else x)\n",
    "sale['季度'] = sale['季度'].apply(lambda x : '第二季度' if x == '04' or x == '05' or x =='06' else x)\n",
    "sale['季度'] = sale['季度'].apply(lambda x : '第三季度' if x == '07' or x == '08' or x =='09' else x)\n",
    "sale['季度'] = sale['季度'].apply(lambda x : '第四季度' if x == '10' or x == '11' or x =='12' else x)\n",
    "sale.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第一季度的销售额和利润"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>利润</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Algeria</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>47.86</td>\n",
       "      <td>1220.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>13.51</td>\n",
       "      <td>450.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>-21.80</td>\n",
       "      <td>1412.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Angola</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>-28.51</td>\n",
       "      <td>467.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>48.87</td>\n",
       "      <td>553.26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        利润      销售额\n",
       "国家      服务分类                       \n",
       "Algeria Commercial   47.86  1220.66\n",
       "        Public       13.51   450.44\n",
       "        Residential -21.80  1412.46\n",
       "Angola  Commercial  -28.51   467.54\n",
       "        Residential  48.87   553.26"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sale_01 = sale[sale['季度']=='第一季度']\n",
    "sale_01_country = sale_01[['国家','服务分类','利润','销售额']].groupby(by=['国家','服务分类']).agg({'利润':'sum','销售额':'sum'})\n",
    "sale_01_region = sale_01[['地区','服务分类','利润','销售额']].groupby(by=['地区','服务分类']).agg({'利润':'sum','销售额':'sum'})\n",
    "sale_01_country.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>利润</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Eastern</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>395.36</td>\n",
       "      <td>16056.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>-98.39</td>\n",
       "      <td>17990.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>240.90</td>\n",
       "      <td>19999.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Middle</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>137.14</td>\n",
       "      <td>10068.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>476.62</td>\n",
       "      <td>11167.73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         利润       销售额\n",
       "地区      服务分类                         \n",
       "Eastern Commercial   395.36  16056.38\n",
       "        Public       -98.39  17990.25\n",
       "        Residential  240.90  19999.58\n",
       "Middle  Commercial   137.14  10068.37\n",
       "        Public       476.62  11167.73"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sale_01_region.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_01_country.to_csv('data/sale_01_country.csv',encoding=\"gbk\")\n",
    "sale_01_region.to_csv('data/sale_01_region.csv',encoding=\"gbk\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 各地区有关服务分类利润数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Eastern</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>573.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>29.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>548.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Middle</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>359.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>461.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>489.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Northern</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>236.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>393.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>593.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southern</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>550.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>461.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>82.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Western</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>-161.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>-165.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>570.93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          利润\n",
       "地区       服务分类               \n",
       "Eastern  Commercial   573.36\n",
       "         Public        29.40\n",
       "         Residential  548.29\n",
       "Middle   Commercial   359.29\n",
       "         Public       461.04\n",
       "         Residential  489.39\n",
       "Northern Commercial   236.27\n",
       "         Public       393.37\n",
       "         Residential  593.75\n",
       "Southern Commercial   550.59\n",
       "         Public       461.94\n",
       "         Residential   82.97\n",
       "Western  Commercial  -161.08\n",
       "         Public      -165.55\n",
       "         Residential  570.93"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sale[['地区','服务分类','利润']].groupby(by=['地区','服务分类']).agg({'利润':'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "quarter_1st = sale[sale['季度']=='第一季度']\n",
    "quarter_2nd = sale[sale['季度']=='第二季度']\n",
    "quarter_3rd = sale[sale['季度']=='第三季度']\n",
    "quarter_4th = sale[sale['季度']=='第四季度']\n",
    "arr1 = [quarter_1st, quarter_2nd, quarter_3rd,quarter_4th ]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 保存各季度的地区销售额和利润"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "arr_1 = ['quarter_1st', 'quarter_2nd', 'quarter_3rd','quarter_4th' ]\n",
    "s = 0\n",
    "for i in arr1:\n",
    "    data = i[['地区','销售额','利润']].groupby(by=['地区']).agg({'销售额':'sum','利润':'sum'})\n",
    "    data.to_csv('data/'+ arr_1[s] + '.csv',encoding=\"gbk\")\n",
    "    s = s+1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 保存各季度的国家销售额和利润"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = quarter_4th\n",
    "a  = a.reset_index(drop = False)\n",
    "\n",
    "a = a[['国家','销售额','利润']].groupby(by='国家').agg({'销售额':'sum','利润':'sum'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=True'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass sort=False\n",
      "\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "s = 0\n",
    "for i in arr1:\n",
    "    data = i[['国家','销售额','利润']].groupby(by=['国家']).agg({'销售额':'sum','利润':'sum'})\n",
    "    if(s>0):\n",
    "        df_0 = pd.concat([df_0,data],axis=1)\n",
    "        df_0.fillna(0, inplace=True)\n",
    "    else:\n",
    "        df_0 = data\n",
    "    s = s+1\n",
    "    a = i"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_0.to_csv('data/quarter_ country.csv',encoding=\"gbk\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 各季度服务分类家销售额和利润"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=True'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass sort=False\n",
      "\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "s = 0\n",
    "for i in arr1:\n",
    "    data = i[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "    if(s>0):\n",
    "        df_1 = pd.concat([df_0,data],axis=1)\n",
    "        df_1.fillna(0, inplace=True)\n",
    "    else:\n",
    "        df_1 = data\n",
    "    s = s+1\n",
    "    a = i"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Algeria</th>\n",
       "      <td>3083.56</td>\n",
       "      <td>39.57</td>\n",
       "      <td>876.49</td>\n",
       "      <td>-26.29</td>\n",
       "      <td>1597.95</td>\n",
       "      <td>56.45</td>\n",
       "      <td>2783.93</td>\n",
       "      <td>-49.27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <td>1020.80</td>\n",
       "      <td>20.36</td>\n",
       "      <td>1869.44</td>\n",
       "      <td>25.50</td>\n",
       "      <td>1739.42</td>\n",
       "      <td>82.14</td>\n",
       "      <td>1451.43</td>\n",
       "      <td>21.34</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Benin</th>\n",
       "      <td>3951.87</td>\n",
       "      <td>286.81</td>\n",
       "      <td>7076.64</td>\n",
       "      <td>-101.44</td>\n",
       "      <td>3929.16</td>\n",
       "      <td>13.03</td>\n",
       "      <td>2022.29</td>\n",
       "      <td>-39.58</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botswana</th>\n",
       "      <td>3176.72</td>\n",
       "      <td>46.58</td>\n",
       "      <td>2184.64</td>\n",
       "      <td>73.20</td>\n",
       "      <td>2123.30</td>\n",
       "      <td>55.90</td>\n",
       "      <td>448.97</td>\n",
       "      <td>-12.11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burkina Faso</th>\n",
       "      <td>5559.06</td>\n",
       "      <td>-46.57</td>\n",
       "      <td>994.43</td>\n",
       "      <td>9.94</td>\n",
       "      <td>2364.71</td>\n",
       "      <td>-106.45</td>\n",
       "      <td>1103.16</td>\n",
       "      <td>32.08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  销售额      利润      销售额      利润      销售额      利润      销售额  \\\n",
       "Algeria       3083.56   39.57   876.49  -26.29  1597.95   56.45  2783.93   \n",
       "Angola        1020.80   20.36  1869.44   25.50  1739.42   82.14  1451.43   \n",
       "Benin         3951.87  286.81  7076.64 -101.44  3929.16   13.03  2022.29   \n",
       "Botswana      3176.72   46.58  2184.64   73.20  2123.30   55.90   448.97   \n",
       "Burkina Faso  5559.06  -46.57   994.43    9.94  2364.71 -106.45  1103.16   \n",
       "\n",
       "                 利润  销售额   利润  \n",
       "Algeria      -49.27  0.0  0.0  \n",
       "Angola        21.34  0.0  0.0  \n",
       "Benin        -39.58  0.0  0.0  \n",
       "Botswana     -12.11  0.0  0.0  \n",
       "Burkina Faso  32.08  0.0  0.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.to_csv('data/quarter_service.csv',encoding=\"gbk\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 分析年度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_2017 = sale[sale['年度']=='2017']\n",
    "sale_2018 = sale[sale['年度']=='2018']\n",
    "sale_2019 = sale[sale['年度']=='2019']\n",
    "sale_2020 = sale[sale['年度']=='2020']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2020年销售额前三国家及年增长率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国家</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Nigeria</th>\n",
       "      <td>8187.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Africa</th>\n",
       "      <td>7971.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Democratic Republic of the Congo</th>\n",
       "      <td>6432.26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      销售额\n",
       "国家                                       \n",
       "Nigeria                           8187.76\n",
       "South Africa                      7971.19\n",
       "Democratic Republic of the Congo  6432.26"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_1 = sale_2020[['国家','销售额']].groupby(by='国家').agg({'销售额':'sum'}).sort_values(['销售额'], ascending = False).head(3)\n",
    "table_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_2 = sale_2019[['国家','销售额']].groupby(by='国家').agg({'销售额':'sum'})\n",
    "merge_data2 = pd.merge(table_1, table_2, on='国家',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>国家</th>\n",
       "      <th>2020年销售额</th>\n",
       "      <th>2019年销售额</th>\n",
       "      <th>增长率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Nigeria</td>\n",
       "      <td>8187.76</td>\n",
       "      <td>5847.10</td>\n",
       "      <td>0.400311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>South Africa</td>\n",
       "      <td>7971.19</td>\n",
       "      <td>8944.40</td>\n",
       "      <td>-0.108807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Democratic Republic of the Congo</td>\n",
       "      <td>6432.26</td>\n",
       "      <td>5335.64</td>\n",
       "      <td>0.205527</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 国家  2020年销售额  2019年销售额       增长率\n",
       "0                           Nigeria   8187.76   5847.10  0.400311\n",
       "1                      South Africa   7971.19   8944.40 -0.108807\n",
       "2  Democratic Republic of the Congo   6432.26   5335.64  0.205527"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_data2['增长率'] = (merge_data2['销售额_x']-merge_data2['销售额_y'])/merge_data2['销售额_y']\n",
    "merge_data2.columns =['2020年销售额','2019年销售额','增长率']\n",
    "merge_data2  = merge_data2.reset_index(drop = False)\n",
    "merge_data2.to_csv('data/merge_data2.csv',encoding=\"gbk\")\n",
    "merge_data2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 每年度地区分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_1 = [sale_2017, sale_2018, sale_2019,sale_2020 ]\n",
    "list_2 = ['sale_2017', 'sale_2018', 'sale_2019','sale_2020' ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = 0\n",
    "for i in list_1:\n",
    "    data_1 = i[['地区','销售额','利润']].groupby(by=['地区']).agg({'销售额':'sum','利润':'sum'})\n",
    "    data_2 = i[['国家','销售额','利润']].groupby(by=['国家']).agg({'销售额':'sum','利润':'sum'})\n",
    "    data_3 = i[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "    if(s>0):\n",
    "        #国家的\n",
    "        data_2['销售额同比增长率'] = (data_2['销售额']-x['销售额'])/x['销售额']\n",
    "        data_2['利润同比增长率'] = (data_2['利润']-x['利润'])/x['利润']\n",
    "        #服务分类的\n",
    "        data_3['销售额同比增长率'] = (data_3['销售额']-y['销售额'])/y['销售额']\n",
    "        data_3['利润同比增长率'] = (data_3['利润']-y['利润'])/y['利润']\n",
    "        #合并表格\n",
    "        table_1 = pd.concat([table_1,data_1],axis=1)\n",
    "        table_2 = pd.concat([table_2,data_2],axis=1)\n",
    "        table_3 = pd.concat([table_3,data_3],axis=1)\n",
    "    else:\n",
    "        table_1 = data_1\n",
    "        table_2 = data_2\n",
    "        table_3 = data_3\n",
    "        \n",
    "    x = data_2\n",
    "    y = data_3\n",
    "    s = s+1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 保存各表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_1.to_csv('data/year_region.csv',encoding=\"gbk\")\n",
    "table_2.to_csv('data/year_country.csv',encoding=\"gbk\")\n",
    "table_3.to_csv('data/year_service.csv',encoding=\"gbk\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.2各地区的销售额同比增长率和利润同比增长率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:19: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=True'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass sort=False\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for i in sale['地区']:\n",
    "    sale_2017_1 = sale_2017[sale_2017['地区']==i]\n",
    "    sale_2017_1 = sale_2017_1[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "\n",
    "    sale_2018_1 = sale_2018[sale_2018['地区']==i]\n",
    "    sale_2018_1 = sale_2018_1[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "    sale_2018_1['销售额同比增长率'] = (sale_2018_1['销售额']-sale_2017_1['销售额'])/sale_2017_1['销售额']\n",
    "    sale_2018_1['利润同比增长率'] = (sale_2018_1['利润']-sale_2017_1['利润'])/sale_2017_1['利润']\n",
    "\n",
    "    sale_2019_1 = sale_2019[sale_2019['地区']==i]\n",
    "    sale_2019_1 = sale_2019_1[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "    sale_2019_1['销售额同比增长率'] = (sale_2019_1['销售额']-sale_2018_1['销售额'])/sale_2018_1['销售额']\n",
    "    sale_2019_1['利润同比增长率'] = (sale_2019_1['利润']-sale_2018_1['利润'])/sale_2018_1['利润']\n",
    "\n",
    "    sale_2020_1 = sale_2020[sale_2020['地区']==i]\n",
    "    sale_2020_1 = sale_2020_1[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "    sale_2020_1['销售额同比增长率'] = (sale_2020_1['销售额']-sale_2019_1['销售额'])/sale_2019_1['销售额']\n",
    "    sale_2020_1['利润同比增长率'] = (sale_2020_1['利润']-sale_2019_1['利润'])/sale_2019_1['利润']\n",
    "    df_0=pd.concat([sale_2017_1,sale_2018_1,sale_2019_1,sale_2020_1])\n",
    "    df_0.fillna(0, inplace=True)\n",
    "    df_0.to_csv('data/'+i+'增长率.csv',encoding=\"gbk\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.2各国家的销售额同比增长率和利润同比增长率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:19: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=True'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass sort=False\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for i in sale['国家']:\n",
    "    sale_2017_1 = sale_2017[sale_2017['国家']==i]\n",
    "    sale_2017_1 = sale_2017_1[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "\n",
    "    sale_2018_1 = sale_2018[sale_2018['国家']==i]\n",
    "    sale_2018_1 = sale_2018_1[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "    sale_2018_1['销售额同比增长率'] = (sale_2018_1['销售额']-sale_2017_1['销售额'])/sale_2017_1['销售额']\n",
    "    sale_2018_1['利润同比增长率'] = (sale_2018_1['利润']-sale_2017_1['利润'])/sale_2017_1['利润']\n",
    "\n",
    "    sale_2019_1 = sale_2019[sale_2019['国家']==i]\n",
    "    sale_2019_1 = sale_2019_1[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "    sale_2019_1['销售额同比增长率'] = (sale_2019_1['销售额']-sale_2018_1['销售额'])/sale_2018_1['销售额']\n",
    "    sale_2019_1['利润同比增长率'] = (sale_2019_1['利润']-sale_2018_1['利润'])/sale_2018_1['利润']\n",
    "\n",
    "    sale_2020_1 = sale_2020[sale_2020['国家']==i]\n",
    "    sale_2020_1 = sale_2020_1[['服务分类','销售额','利润']].groupby(by=['服务分类']).agg({'销售额':'sum','利润':'sum'})\n",
    "    sale_2020_1['销售额同比增长率'] = (sale_2020_1['销售额']-sale_2019_1['销售额'])/sale_2019_1['销售额']\n",
    "    sale_2020_1['利润同比增长率'] = (sale_2020_1['利润']-sale_2019_1['利润'])/sale_2019_1['利润']\n",
    "    df_0=pd.concat([sale_2017_1,sale_2018_1,sale_2019_1,sale_2020_1])\n",
    "    df_0.fillna(0, inplace=True)\n",
    "    df_0.to_csv('data/'+i+'国家增长率.csv',encoding=\"gbk\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 分析销售"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售经理</th>\n",
       "      <th>销售合同</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aiden Morris</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Audrey Baker</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Constantine Eager</td>\n",
       "      <td>161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Francis Martineau</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>George O'Malley</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ken Railings</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Laura Yeager</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Marianne James</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Michael Smith</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Priscilla Taylor</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                销售经理  销售合同\n",
       "0       Aiden Morris   190\n",
       "1       Audrey Baker   203\n",
       "2  Constantine Eager   161\n",
       "3  Francis Martineau   151\n",
       "4    George O'Malley   169\n",
       "5       Ken Railings   120\n",
       "6       Laura Yeager   125\n",
       "7     Marianne James   118\n",
       "8      Michael Smith    60\n",
       "9   Priscilla Taylor   195"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contract.groupby(by=['销售经理']).agg({'销售合同':'sum'}).reset_index(drop = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "contract[\"总合同\"] = round(contract['销售合同']/contract['成交率'],0)\n",
    "contract_new = contract.groupby(by=['销售经理']).agg({'销售合同':'sum','总合同':'sum'})\n",
    "contract_new['成交率'] = round(contract_new['销售合同']/contract_new['总合同'],2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 找出前三销售经理的全部数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售经理</th>\n",
       "      <th>销售合同</th>\n",
       "      <th>总合同</th>\n",
       "      <th>成交率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Audrey Baker</td>\n",
       "      <td>203</td>\n",
       "      <td>541.0</td>\n",
       "      <td>0.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Priscilla Taylor</td>\n",
       "      <td>195</td>\n",
       "      <td>504.0</td>\n",
       "      <td>0.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Aiden Morris</td>\n",
       "      <td>190</td>\n",
       "      <td>402.0</td>\n",
       "      <td>0.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               销售经理  销售合同    总合同   成交率\n",
       "0      Audrey Baker   203  541.0  0.38\n",
       "1  Priscilla Taylor   195  504.0  0.39\n",
       "2      Aiden Morris   190  402.0  0.47"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contract_new.sort_values(['销售合同'], ascending = False).reset_index(drop = False).head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 销售经理的销售合同数前 5 名排行榜"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "contract_new = contract_new.sort_values(['销售合同'], ascending = False).reset_index(drop = False).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "contract_new.to_csv('data/contract_new.csv',encoding=\"gbk\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.3 预测2021销售"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 预测-季度_年份_地区----销售额为"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " [12672.48189756  7777.34516818  4993.46488136  5791.98032302\n",
    " 13775.34309191]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 预测-季度_年份_国家----利润为"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " [11.7258186  11.07862841 11.33216923 12.58897082 13.50756334 11.28229615\n",
    " 11.42588422 10.84062944 11.61540192 12.20158458  9.47085952 11.91997566\n",
    " 11.22523207 12.46092459 11.63651099 13.21551587 11.43887442 12.23336417\n",
    " 11.53235745 11.65831597 11.89863462 11.74228831 11.44792116 11.47668517\n",
    " 11.96126593 11.55532226 12.40757199 11.42333258 11.1579614  10.33911503\n",
    " 13.08097454  9.73576676 11.95848231 12.19624932 12.85944528 12.0371194\n",
    " 11.63511919 11.74344815 12.35932269 12.53097887 10.39084386 11.95152328\n",
    " 11.47274172 13.77826978 10.88911071 12.49943124 11.69427098 10.65760684\n",
    " 12.82209847 12.08142525 11.88518048 12.03920711]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 预测-季度_年份_国家-----销售额为"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[ 698.8504973   702.5848223   747.52870477  781.0328203   979.4450387\n",
    "  650.67692834 1137.16010182  902.54201092  701.28828951  591.89430507\n",
    " 1113.19365421 1235.96055929 1038.35188068  641.62837159  792.43765073\n",
    "  852.10145048  771.94156337  605.18182534  932.03230934  736.39948461\n",
    "  825.50311893  878.96762863  751.96564187  986.11791259  567.50085564\n",
    "  774.158091   1071.27760283  845.2965942   892.66662338  916.27982402\n",
    "  872.65964719 1199.10253856  591.6070493  1189.14563248 1761.27372554\n",
    "  525.20050262  966.825194    693.03939071  702.60811331  724.97136317\n",
    "  976.55307183 1418.15059036  694.61929744 1222.18004601  977.66327656\n",
    "  909.16442097  596.16044143  867.13579638  742.35421908  559.4033484\n",
    "  825.6001648   854.53147902]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 预测-季度_年份_地区-----利润为"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " [ 97.98569254 117.75810997 146.64459299 128.83403852 120.0798792 ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 预测-季度_年份_服务分类----利润为"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[223.37534749 223.46876797 223.14509526]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 预测-季度_年份_服务分类----销售额为"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " [15787.06290759 15787.0075854  15786.99847013]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2021第一季度 地区—销售额地区为：Eastern,Middle,Northern,Southern,Western 得到公式为：y=wx+b,其中w=[0.73416399],b=[-0.00038272] \n",
    "销售额：[12673.63412319 7784.98204843 5004.78960593 5802.24724325 13775.03434222] \n",
    "2021第一季度 地区—利润地区为：Eastern,Middle,Northern,Southern,Western \n",
    "得到公式为：y=wx+b,其中w=[-0.10973225],b=[-0.00016252] \n",
    "利润：[97.17482493 117.89908653 148.17616483 129.50821027 120.33262573\n",
    "2021第一季度 国家—销售额国家为：['Algeria', 'Angola', 'Benin', 'Botswana', 'Burkina Faso', 'Burundi', 'Cameroon', 'Central African Republic', 'Chad', 'Congo', 'Cote d'Ivoire', 'Democratic Republic of the Congo', 'Djibouti', 'Egypt', 'Equatorial Guinea', 'Eritrea', 'Ethiopia', 'Gabon', 'Ghana', 'Guinea', 'Guinea-Bissau', 'Kenya', 'Lesotho', 'Liberia', 'Libya', 'Madagascar', 'Malawi', 'Mali', 'Mauritania', 'Mauritius', 'Morocco', 'Mozambique', 'Namibia', 'Niger', 'Nigeria', 'Rwanda', 'Sao Tome and Principe', 'Senegal', 'Seychelles', 'Sierra Leone', 'Somalia', 'South Africa', 'South Sudan', 'Sudan', 'Swaziland', 'Togo', 'Tunisia', 'Uganda', 'United Republic of Tanzania', 'Western Sahara', 'Zambia', 'Zimbabwe' \n",
    "得到公式为：y=wx+b,其中w=[0.40497671],b=[-0.00048859] 销售额为：[ 697.90762315 701.65801571 746.79527675 780.44354933 979.70946948 649.52677935 1138.10312803 902.47555357 700.35590436 590.49123401 1114.03356078 1237.32869099 1038.869768 640.43928967 791.89745095 851.81796418 771.31317575 603.83592606 932.09273893 735.61817125 825.10518886 878.79973855 751.2513045 986.41105453 565.99282769 773.53924036 1071.93715857 844.98382887 892.55767552 916.2724759 872.46461597 1200.31208246 590.20274228 1190.31233513 1764.90210497 523.51047033 967.03532579 692.0715133 701.68140693 724.14087842 976.80505943 1420.30262513 693.65821785 1223.48888474 977.92004101 909.12645764 594.77572614 866.91699786 741.59852697 557.86047958 825.20265229 854.25844832] \n",
    "2021第一季度 国家—利润国家为：['Algeria', 'Angola', 'Benin', 'Botswana', 'Burkina Faso', 'Burundi', 'Cameroon', 'Central African Republic', 'Chad', 'Congo', 'Cote d'Ivoire', 'Democratic Republic of the Congo', 'Djibouti', 'Egypt', 'Equatorial Guinea', 'Eritrea', 'Ethiopia', 'Gabon', 'Ghana', 'Guinea', 'Guinea-Bissau', 'Kenya', 'Lesotho', 'Liberia', 'Libya', 'Madagascar', 'Malawi', 'Mali', 'Mauritania', 'Mauritius', 'Morocco', 'Mozambique', 'Namibia', 'Niger', 'Nigeria', 'Rwanda', 'Sao Tome and Principe', 'Senegal', 'Seychelles', 'Sierra Leone', 'Somalia', 'South Africa', 'South Sudan', 'Sudan', 'Swaziland', 'Togo',"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
